package chat.server.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.sql.*;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DbHelper {

    private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
    private static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/chat?useUnicode=true&characterEncoding=utf8";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "qhm123";
    private static DbHelper helper;
    private Connection conn;

    public static DbHelper getInstance() {
        if (helper == null) {
            helper = new DbHelper();
        }

        return helper;
    }

    public void close() {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private DbHelper() {
        try {
            Class.forName(DB_DRIVER);
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
            if (!conn.isClosed()) {
                System.out.println("Succeeded connecting to the Database!");
            }
        } catch (ClassNotFoundException e) {
            System.out.println("Sorry,can`t find the Driver!");
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public User register(String uid, String password, String nick) {
        User user = null;
        try {
            Statement st = conn.createStatement();
            String sql = String.format("INSERT INTO user (uid, nick, password) VALUES(\"%s\", \"%s\", \"%s\")", uid, nick, password);
            st.executeUpdate(sql);
            st.close();
            user = new User(uid, nick, password);
        } catch (SQLException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return user;
    }

    public void changeProfile(String uid, String nick) {
        try {
            Statement st = conn.createStatement();
            String sql = String.format("UPDATE user SET nick=\"%s\" WHERE uid=\"%s\"", nick, uid);
            st.executeUpdate(sql);
            st.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void changePassword(String uid, String password) {
        try {
            Statement st = conn.createStatement();
            String sql = String.format("UPDATE user SET password=\"%s\" WHERE uid=\"%s\"", password, uid);
            st.executeUpdate(sql);
            st.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public boolean addFriend(String uid1, String uid2) {
        try {
            Statement st = conn.createStatement();
            String sql = String.format("INSERT INTO relation (uid1, uid2) VALUES(\"%s\", \"%s\")", uid1, uid2);
            st.executeUpdate(sql);
            st.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        }

        return true;
    }

    public User getUserByUid(String uid) throws SQLException {
        User user = null;

        Statement statement = conn.createStatement();
        String sql = String.format("select * from user where uid = \"%s\"", uid);
        ResultSet rs = statement.executeQuery(sql);
        String nick = null;
        String passwd = null;
        if (rs.next()) {
            nick = rs.getString("nick");
            passwd = rs.getString("password");
        }
        rs.close();
        user = new User(uid, nick, passwd);

        System.out.println(user.uid + "\t" + user.nick);
        return user;
    }

    public void updateOnline(String uid, boolean online) {
        try {
            Statement sm = conn.createStatement();
            String sql = String.format("UPDATE user SET online = \"%s\" WHERE uid = \"%s\"", online ? "Y" : "N", uid);
            sm.executeUpdate(sql);
            sm.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public boolean getUserOnline(String uid) {
        boolean online = false;
        try {
            Statement sm = conn.createStatement();
            String sql = String.format("SELECT * FROM user WHERE uid = \"%s\"", uid);
            ResultSet rs = sm.executeQuery(sql);
            if (rs.next()) {
                online = rs.getString("online").equals("Y") ? true : false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return online;
    }

    public void updateIPByUid(String ip, String uid) {
        try {
            Statement statement = conn.createStatement();
            String sql = String.format("UPDATE user SET ip = \"%s\" where uid = \"%s\"", ip, uid);
            statement.executeUpdate(sql);
            statement.close();
        } catch (SQLException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public String getIPByUid(String uid) {
        String ip = null;
        try {
            Statement statement = conn.createStatement();
            String sql = String.format("SELECT ip FROM user WHERE uid = \"%s\"", uid);
            ResultSet rs = statement.executeQuery(sql);

            if (rs.next()) {
                ip = rs.getString("ip");
            }
            rs.close();
            System.out.println(String.format("ip: %s.", ip));

        } catch (SQLException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ip;
    }

    public Vector<User> getAllUserNotFriends(String uid, boolean fitlerOnline) {
        Vector<User> friends = new Vector<User>();
        try {
            Statement statement = conn.createStatement();
            String sql = String.format("select nick,uid from user where uid not in (select uid2 from relation, user where uid = \"%s\" and uid = uid1) AND uid != \"%s\"", uid, uid);
            ResultSet rs = statement.executeQuery(sql);
            String uuid = null;
            String nick = null;
            while (rs.next()) {
                nick = rs.getString("nick");
                uuid = rs.getString("uid");
                boolean online = getUserOnline(uuid);
                User user = new User(uuid, nick, "");
                user.online = online;
                friends.add(user);
            }
            rs.close();
            System.out.println(String.format("friends count: %d", rs.getFetchSize()));

        } catch (SQLException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return friends;
    }

    public Vector<User> getFriendsByUid(String uid) {
        Vector<User> friends = new Vector<User>();
        try {
            Statement statement = conn.createStatement();
            String sql = String.format("select nick,uid from user where uid in (select uid2 from relation, user where uid = \"%s\" and uid = uid1)", uid);
            ResultSet rs = statement.executeQuery(sql);
            String uuid = null;
            String nick = null;
            while (rs.next()) {
                nick = rs.getString("nick");
                uuid = rs.getString("uid");
                boolean online = getUserOnline(uuid);
                User user = new User(uuid, nick, "");
                user.online = online;
                friends.add(user);
            }
            rs.close();
            System.out.println(String.format("friends count: %d", rs.getFetchSize()));

        } catch (SQLException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        return friends;
    }

    public static void main(String arg[]) {
        DbHelper helper = DbHelper.getInstance();
        try {
            helper.getUserByUid("mingq");
        } catch (SQLException ex) {
            Logger.getLogger(DbHelper.class.getName()).log(Level.SEVERE, null, ex);
        }
        helper.close();
    }
}
